/***********************************************************************************************************************************
 * Name:	Index_PageLocksON.sql
 * Author:	Frank Figearo (frank@sqlnerd.me)
 * Summary:	Alter all indexes in the database to allow page locks and row locks.
 */
SET IMPLICIT_TRANSACTIONS OFF; WHILE 0 < @@TRANCOUNT ROLLBACK;
DECLARE @tsql_command NVARCHAR(MAX) = N'';
SELECT @tsql_command+= N'ALTER INDEX ALL ON ['+SCHEMA_NAME(schema_id)+ N'].[' + OBJECT_NAME(object_id) + N'] SET (ALLOW_PAGE_LOCKS=ON,ALLOW_ROW_LOCKS=ON); ' FROM sys.tables ORDER BY [object_id] DESC;
PRINT  @tsql_command;
EXECUTE (@tsql_command);
GO